﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace LUStationery.DAO
{
    public class PoDAO
    {
        SqlConnection cn;
        SqlCommand cmd;
        string sqlStr;
        //ZHONG YUE\\
        public IDataReader addPo(string supplierCode,string supplierName,double totalPrice,string status,DateTime expectedDate,DateTime orderDate,string orderBy,string deliverTo,string attn)
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            sqlStr="insert into purchase_order (supplier_code,supplier_name,total_price,po_status,expected_date,order_date,order_by,deliver_to,attn) values (@supplierCode, @supplierName,@totalPrice,@status,@expectedDate,@orderDate,@orderBy,@deliverTo,@attn)"+"select @@identity as newPoID";
            cmd = new SqlCommand(sqlStr, cn);
            cmd.Parameters.Add("@supplierCode", SqlDbType.VarChar).Value = supplierCode;
            cmd.Parameters.Add("@supplierName", SqlDbType.VarChar).Value = supplierName;
            cmd.Parameters.Add("@totalPrice", SqlDbType.VarChar).Value = totalPrice;
            cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
            cmd.Parameters.Add("@expectedDate", SqlDbType.VarChar).Value = expectedDate;
            cmd.Parameters.Add("@orderDate", SqlDbType.VarChar).Value = orderDate;
            cmd.Parameters.Add("@orderBy", SqlDbType.VarChar).Value = orderBy;
            cmd.Parameters.Add("@deliverTo", SqlDbType.VarChar).Value = deliverTo;
            cmd.Parameters.Add("@attn", SqlDbType.VarChar).Value = attn;
            cn.Open();
            int poID=Convert.ToInt32(cmd.ExecuteScalar());
            return getPoByPoID(poID);
        }
        public IDataReader getPoByPoID(string poID)
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            cmd = new SqlCommand("Select * from purchase_order where purchase_order_id=@poID", cn);
            cmd.Parameters.Add("@poID", SqlDbType.VarChar).Value = poID;
            //cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
           // cn.Close();
            return idr;
            
        }

        //ZHONG YUE\\
        public void updateTotalPrice(int purchaseOrderID,double totalPrice)
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            cmd = new SqlCommand("update purchase_order set total_price=@totalPrice where purchase_order_id=@purchaseOrderID", cn);
            cmd.Parameters.Add("@purchaseOrderID", SqlDbType.VarChar).Value = purchaseOrderID;
            cmd.Parameters.Add("@totalPrice", SqlDbType.VarChar).Value = totalPrice;
            cn.Open();
            cmd.ExecuteNonQuery();
        }

        public IDataReader getPoIDs()
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            cmd = new SqlCommand("Select * from purchase_order", cn);
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            //cn.Close();
            return idr;

        }

    }
}
